package dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import model.Course;
import model.Teacher;
import util.DBUtil;
import util.Page;
import dao.TeacherDAO;

public class TeacherDAOImpl implements TeacherDAO {
	DBUtil dbu = new DBUtil();
	Connection conn = null;
	PreparedStatement pstat = null;
	ResultSet rs = null;
	public List<Teacher> getAll() {
		List <Teacher> list=new ArrayList<Teacher>();
		try {
			conn = dbu.getConnection();
			String sql="select * from teacher";
			pstat=conn.prepareStatement(sql);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Teacher  t = new Teacher();
				t.setId(rs.getInt("ID"));
				t.setName(rs.getString("NAME"));
				list.add(t);
			}
		}catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return list;
	}

	public Teacher getById(int id) {
		Teacher  t = new Teacher();
		try {
			conn = dbu.getConnection();
			String sql="select name from teacher where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1, id);
			rs = pstat.executeQuery();
			while (rs.next()) {
				t.setId(id);
				t.setName(rs.getString("name"));
			}
		}
			catch (Exception e) {
				e.printStackTrace();
			} finally {
				dbu.closeConnection(conn, pstat, rs);
			}
		return t;
	}

	public List<Teacher> getByName(String name) {
		// TODO Auto-generated method stub
		return null;
	}

	public Page<Teacher> getAll(int pageNo, int pageSize) {
		Page<Teacher> page = new Page<Teacher>(pageNo, pageSize);
		int totalCount = 0;
		List<Teacher> list = new ArrayList<Teacher>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from teacher";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select id,name from teacher) s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Teacher  t = new Teacher();
				t.setId(rs.getInt("ID"));
				t.setName(rs.getString("NAME"));
				list.add(t);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;		
	}

	public boolean add(Teacher t) {
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql="insert into teacher(ID,NAME) values(seq.nextval,?)";
			pstat=conn.prepareStatement(sql);
			pstat.setString(1, t.getName());
		    pstat.executeUpdate();
		    flag=true;
		}
			catch (Exception e) {
				e.printStackTrace();
			} finally {
				dbu.closeConnection(conn, pstat, rs);
			}
		return flag;
	}

	public boolean update(Teacher t) {
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql="update teacher set name=? where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setString(1, t.getName());
			pstat.setInt(2, t.getId());
		    int i=pstat.executeUpdate();
		    if(i>0)
		    {
		    flag=true;
		    }
		}
			catch (Exception e) {
				e.printStackTrace();
			} finally {
				dbu.closeConnection(conn, pstat, rs);
			}
		return flag;
	}

	public boolean deleteById(int id) {
		boolean flag=false;
		try {
			conn = dbu.getConnection();
			String sql="delete from teacher where id=?";
			pstat=conn.prepareStatement(sql);
			pstat.setInt(1,id);
		    pstat.executeUpdate();
		    flag=true;
		}
			catch (Exception e) {
				e.printStackTrace();
			} finally {
				dbu.closeConnection(conn, pstat, rs);
			}
		return flag;
	}

	public Page<Teacher> getByName(int pageNo, int pageSize, String name) {
		Page<Teacher> page = new Page<Teacher>(pageNo, pageSize);
		int totalCount = 0;
		List<Teacher> list = new ArrayList<Teacher>();
		try {
			conn = dbu.getConnection();
			String sql="select count(*) from teacher where name like'%"+name+"%'";
			pstat=conn.prepareStatement(sql);
			rs=pstat.executeQuery();
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			page.setTotalCount(totalCount);
			sql = "select * from (select s.*,rownum rn from (select id,name from teacher where name like'%"+name+"%') s where rownum <=?) where rn >=?";
			pstat = conn.prepareStatement(sql);
			pstat.setInt(1, page.getPageNo()*pageSize);
			pstat.setInt(2,(page.getPageNo()-1)*pageSize+1);
			rs = pstat.executeQuery();
			while (rs.next()) {
				Teacher  t = new Teacher();
				t.setId(rs.getInt("ID"));
				t.setName(rs.getString("NAME"));
				list.add(t);
			}
			page.setList(list);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			dbu.closeConnection(conn, pstat, rs);
		}
		return page;		
	}
}
